package com.warm.pump.module.work.controller;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.warm.pump.frame.db.DbUtil;
import com.warm.pump.module.work.bean.WorkCopyTable;
import org.apache.commons.collections.MapUtils;
import org.apache.ddlutils.Platform;
import org.apache.ddlutils.PlatformFactory;
import org.apache.ddlutils.model.Database;
import org.apache.ddlutils.model.Table;
import org.apache.shiro.authz.annotation.RequiresPermissions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.stereotype.Controller;

import com.warm.pump.frame.gen.bean.PageBean;
import com.warm.pump.module.work.bean.po.gen.WorkDb;
import com.warm.pump.module.work.service.IWorkDbService;
import com.warm.pump.frame.gen.util.OutJson;
import com.warm.pump.frame.gen.util.MyBeanUtils;
import org.apache.commons.lang3.StringUtils;

import javax.sql.DataSource;

/**
 * work_db
 */
@Controller
@RequestMapping("/workDb")
public class WorkDbController {
    @Autowired
    private IWorkDbService workDbService;

    /**
     * 列表
     */
    @ResponseBody
    @RequestMapping("/list")
    @RequiresPermissions("workDb:list")
    public OutJson list(Integer page, Integer limit, WorkDb workDb) {
        Map<String, Object> map = new HashMap<>();
        MyBeanUtils.copyBean2Map(map, workDb);

        if (StringUtils.isNotEmpty(workDb.getSidx()) && StringUtils.isNotEmpty(workDb.getOrder())) {
            map.put("orderBy", workDb.getSidx() + " " + workDb.getOrder());
        }
        //查询列表数据
        PageBean pageBean = workDbService.getPageBeanByMap(map, page, limit);

        return OutJson.ok().put("page", pageBean);
    }


    /**
     * 信息
     */
    @ResponseBody
    @RequestMapping("/info/{id}")
    @RequiresPermissions("workDb:info")
    public OutJson info(@PathVariable("id") Long id) {
        WorkDb workDb = workDbService.queryObject(id);

        return OutJson.ok().put("workDb", workDb);
    }


    /**
     * 保存
     */
    @ResponseBody
    @RequestMapping("/save")
    @RequiresPermissions("workDb:save")
    public OutJson save(@RequestBody WorkDb workDb) {
        workDbService.save(workDb);

        return OutJson.ok();
    }

    /**
     * 修改
     */
    @ResponseBody
    @RequestMapping("/update")
    @RequiresPermissions("workDb:update")
    public OutJson update(@RequestBody WorkDb workDb) {
        workDbService.update(workDb);

        return OutJson.ok();
    }

    /**
     * 删除
     */
    @ResponseBody
    @RequestMapping("/delete")
    @RequiresPermissions("workDb:delete")
    public OutJson delete(@RequestBody Long[] ids) {
        workDbService.deleteBatch(ids);

        return OutJson.ok();
    }


    @ResponseBody
    @RequestMapping("/testConnection")
    public OutJson testConnection(String url, String pass, String user) {
        boolean flag = workDbService.testConnection(url, user, pass);
        return OutJson.ok().setResData(flag ? 1 : 0);
    }

    /**
     * 通过dbkey获取表名列表
     *
     * @param dbKey
     * @param qryName
     * @return
     */
    @ResponseBody
    @RequestMapping("/qryTablesByDbKey/{dbKey}")
    public List<Map<String, Object>> qryTablesByDbKey(@PathVariable("dbKey") String dbKey, String qryName) {

        List<Map<String, Object>> tablesFromDb = DbUtil.getTablesFromDb(dbKey, qryName);
        List<Map<String, Object>> rtList = new ArrayList<>();
        for (Map<String, Object> tables : tablesFromDb) {
            String tableName = MapUtils.getString(tables, "table_name");
            String tableComment = MapUtils.getString(tables, "table_comment");

            Map<String, Object> rtMap = new HashMap<>();
            rtMap.put("id", tableName);
            rtMap.put("value", tableName);
            rtMap.put("label", tableComment);
            rtList.add(rtMap);
        }
        return rtList;
    }

    @ResponseBody
    @RequestMapping("/qrySelectSqlByTableName")
    public OutJson qrySelectSqlByTableName(String dbKey, String qryName) {

        String selectSqlByTableName = DbUtil.getSelectSqlByTableName(dbKey, qryName);
        return OutJson.ok().put("selectSql", selectSqlByTableName);
    }


    @ResponseBody
    @RequestMapping("/qryInsertSqlByTableName")
    public OutJson qryInsertSqlByTableName(String dbKey, String qryName) {

        String insertSqlByTableName = DbUtil.getInsertSqlByTableName(dbKey, qryName);
        return OutJson.ok().put("insertSql", insertSqlByTableName);
    }

    /**
     * 复制表
     *
     * @param workCopyTable
     * @return
     */
    @ResponseBody
    @RequestMapping("/copyTable")
    public OutJson copyTable(@RequestBody WorkCopyTable workCopyTable) {

        //获取from数据源
        DataSource dataSourceFrom = DbUtil.getDataSource(workCopyTable.getDbKeyFrom());
        //获取to数据源
        DataSource dataSourceTo = DbUtil.getDataSource(workCopyTable.getDbKeyTo());
        Connection conn = null;
        try {
            Platform platform = PlatformFactory.createNewPlatformInstance(dataSourceFrom);
            conn = platform.getDataSource().getConnection();
            String catalog = conn.getCatalog();
            Database db = platform.readModelFromDatabase(catalog);
            Table tableFrom = db.findTable(workCopyTable.getTableFrom());
            tableFrom.setName(workCopyTable.getTableTo());

            Platform platformTo = PlatformFactory.createNewPlatformInstance(dataSourceTo);
            Database db2 = new Database();
            db2.addTable(tableFrom);
            platformTo.createTables(db2, false, false);
        } catch (Exception e) {
            e.printStackTrace();
            return OutJson.error("复制失败，详情请看后台日志。");
        } finally {
            try {
                if (conn != null && !conn.isClosed()) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
                return OutJson.error("复制失败，详情请看后台日志。");
            }
        }
        return OutJson.ok();
    }

}
